
if exists (select 1 from sysobjects where name = 'get_saldo' and type = 'P')
begin
   drop procedure get_saldo
   print 'Procedure: get_saldo deleted ...'
end
go
create procedure get_saldo(
  @prsid              int           = 1,
  @planid             int           = 25,
  @abteilungid        int           = 1
)
as
begin
  set nocount on

  declare @date datetime
  declare @vortrag decimal(5,2)
  
  select @date = Von from ArbeitsPlan where PlanID = @planid

  create table #saldo(
    saldo      decimal(7,2)
  )

  insert #saldo
  select saldo = isnull(sum((Haben + Ferien) - Soll),0) from Arbeitszeit az
   inner join ArbeitsPlan ap
      on ap.PlanID = az.PlanID
   where az.PrsID = @prsid 
     and az.AbteilungID = @abteilungid
     and ap.Bis < @date
     and Soll > 0

  select @date = Von from ArbeitsPlan where PlanID = @planid

  insert #saldo
  select saldo = sum(Stunden)
    from Saldoanpassung
   where PrsID = @prsid
     and AbteilungID = @abteilungid
     and Datum < @date

   select @vortrag = sum(isnull(saldo,0))
     from #saldo
          
  declare @soll     decimal(7,2)
  declare @haben    decimal(7,2)
  declare @ferien   decimal(7,2)
  declare @anpassung decimal(7,2)
     
   select @soll = isnull(Soll,0)
     from Arbeitszeit
    where PrsID = @prsid
      and PlanID = @planid
      and AbteilungID = @abteilungid
      
   select @haben = isnull(Haben,0)
     from Arbeitszeit
    where PrsID = @prsid
      and PlanID = @planid
      and AbteilungID = @abteilungid
      
   select @ferien = isnull(Ferien,0)
     from Arbeitszeit
    where PrsID = @prsid
      and PlanID = @planid
      and AbteilungID = @abteilungid
      
   select @anpassung = ISNULL(sum(isnull(Stunden,0)),0)
    from Saldoanpassung 
   where PrsID = @prsid
     and AbteilungID = @abteilungid
     and PlanID = @planid
      
   select vortrag = isnull(@vortrag,0),
          soll = isnull(@soll,0),
          haben = isnull(@haben,0),
          ferien = isnull(@ferien,0),
          anpassung = isnull(@anpassung,0)


end
go
print 'Procedure: get_saldo done ...'
go
grant exec on get_saldo to prsadmins with grant option
go
grant exec on get_saldo to prsusers
go

